package modbusGreen

import (
    "errors"
    "github.com/astaxie/beego/orm"
    _ "github.com/go-sql-driver/mysql"
    "reflect"
    "strconv"
    "strings"
)

func init()  {
   InitDB()
   RegisterDB()
}

//init database
func InitDB() (result bool, err error) {
   db := make(map[string]string, 5)
   db["mode"]      = "mysql"
   db["addr"]      = "127.0.0.1:3306"
   db["user"]      = "root"
   db["password"]  = "green"
   db["datebase"]  = "ganlin"

   // choice driver
   if err := orm.RegisterDriver("mysql", orm.DRMySQL);
       err != nil {
       return false, errors.New("RegisterDriver error")
   }

   // set default database
   if err = orm.RegisterDataBase("default", "mysql", "root:green@tcp(127.0.0.1:3306)/modbus?charset=utf8");
       err != nil {
       return false, errors.New("RegisterDataBase error")
   }

   return true, nil
}

func RegisterDB() (err error) {

   // register model
   orm.RegisterModel(new(Register))
   orm.RegisterModel(new(RegisterRecord))
   orm.RegisterModel(new(SensorType))
   orm.RegisterModel(new(Sensor))

   // create table
   err = orm.RunSyncdb("default", false, true)
   if err != nil {
       return errors.New("create table error")
   }

   return nil
}

// DB
type DB struct {
    orm.Ormer
}


//INSERT INSERT
func (O *DB) INSERT(tableName string, data interface{}) error {
    sql, v := GetInsertSQL(tableName, data)
    _, err := O.Raw(sql, v...).Exec()
    if err != nil {
        //DBLOG.Debug(sql, v, err)
        return err
    }
    //DBLOG.Info(sql, v)
    return nil

}

//UPDATE UPDATE
func (O *DB) UPDATE(tableName string, data interface{}, condition string, args ...interface{}) error {
    sql, v := GetUpdateSQL(tableName, data)
    v = append(v, args...)
    sql += " " + condition + ";"
    _, err := O.Raw(sql, v...).Exec()
    if err != nil {
        //DBLOG.Debug(sql, v, err)
        return err
    }
    //DBLOG.Info(sql, v, args)
    return nil

}

//DELETE DELETE
func (O *DB) DELETE(tableName string, condition string, args ...interface{}) error {
    sql := "DELETE FROM " + tableName + " " + condition + ";"
    _, err := O.Raw(sql, args...).Exec()
    if err != nil {
        //DBLOG.Debug(sql, args, err)
        return err
    }
    //DBLOG.Info(sql, args)
    return nil

}

//SELECTS SELECTS
func (O *DB) SELECTS(tableName, condition string, data interface{}, arg ...interface{}) error {
    sql := GetSelectsSQL(tableName, data)
    sql += " " + condition
    _, err := O.Raw(sql, arg...).QueryRows(data)
    if err != nil {
        //DBLOG.Debug(sql, arg, err)
        return err
    }
    val := reflect.ValueOf(data)
    sInd := reflect.Indirect(val)
    if sInd.IsNil() {
        val.Elem().Set(reflect.MakeSlice(sInd.Type(), 0, 0))
    }
    //DBLOG.Info(sql, arg)
    return nil

}

//SELECT SELECT
func (O *DB) SELECT(tableName, condition string, data interface{}, arg ...interface{}) error {
    sql := GetSelectSQL(tableName, data)
    sql += " " + condition
    err := O.Raw(sql, arg...).QueryRow(data)
    if err != nil {
        //DBLOG.Debug(sql, arg, err)
        return err
    }
    //DBLOG.Info(sql, arg)
    return nil

}


//SELECTSLIMIT SELECTS
func (O *DB) SELECTSLIMIT(tableName, key, condition, other string, offset, number int, data interface{}, arg ...interface{}) error {
    qsql := GetSelectsLIMITSQL(tableName, data)
    sql := "SELECT TOP " + strconv.Itoa(number) + " " + qsql + " " + condition
    if strings.Index(condition, "WHERE") == -1 {
        sql += " WHERE " + key + " not in (SELECT TOP " + strconv.Itoa(offset) + " " + key + " FROM " + tableName + " " + condition + " " + other + ") "
    } else {
        sql += " AND " + key + " not in (SELECT TOP " + strconv.Itoa(offset) + " " + key + " FROM " + tableName + " " + condition + " " + other + ") "
    }
    sql += " " + other
    l := len(arg)

    targ := make([]interface{}, 2*l)
    for i, a := range arg {
        targ[i] = a
        targ[i+l] = a
    }
    //DBLOG.Debug(targ)
    _, err := O.Raw(sql, targ...).QueryRows(data)
    if err != nil {
        //DBLOG.Debug(sql, arg, err)
        return err
    }
    val := reflect.ValueOf(data)
    sInd := reflect.Indirect(val)
    if sInd.IsNil() {
        val.Elem().Set(reflect.MakeSlice(sInd.Type(), 0, 0))
    }
    //DBLOG.Info(sql, arg)
    return nil

}

//SELECTNUM 查询表NUM
func (O *DB) SELECTNUM(tablename, condition, key string, data interface{}, arg ...interface{}) error {
    sql := "SELECT COUNT(" + key + ") FROM " + tablename + " " + condition
    err := O.Raw(sql, arg...).QueryRow(data)
    if err != nil {
        //DBLOG.Debug(sql, arg, err)
        return err
    }
    //DBLOG.Info(sql, arg)
    return nil
}

//GetInsertSQL 获取插入的sql
func GetInsertSQL(tableName string, data interface{}) (sql string, v []interface{}) {

    typ := reflect.TypeOf(data)
    val := reflect.ValueOf(data)
    if typ.Kind() != reflect.Ptr {
        return "", nil
    }

    sql = "INSERT INTO " + tableName + " ("
    var names []string
    var vales []string
    for i := 0; i < typ.Elem().NumField(); i++ {
        name := GetFieldName(typ.Elem().Field(i), sqlTag)
        if name[0] == "-" {
            continue
        }
        if isEmptyValue(val.Elem().Field(i)) {
            if len(name) > 1 && (name[1] == "key" || name[1] == "null") {
                continue
            }
        }
        names = append(names, name[0])
        vales = append(vales, "?")
        v = append(v, val.Elem().Field(i).Interface())
    }
    sql += strings.Join(names, ",") + ") VALUES(" + strings.Join(vales, ",") + ");"
    return
}

//GetUpdateSQL 获取修改sql
func GetUpdateSQL(tableName string, data interface{}) (sql string, v []interface{}) {
    typ := reflect.TypeOf(data)
    val := reflect.ValueOf(data)
    if typ.Kind() != reflect.Ptr {
        return "", nil
    }

    sql = "UPDATE " + tableName + " SET "
    var names []string
    for i := 0; i < typ.Elem().NumField(); i++ {
        name := GetFieldName(typ.Elem().Field(i), sqlTag)
        if name[0] == "-" {
            continue
        }
        if isEmptyValue(val.Elem().Field(i)) {
            if len(name) > 1 && (name[1] == "key" || name[1] == "null") {
                continue
            }
        }
        names = append(names, name[0])
        v = append(v, val.Elem().Field(i).Interface())
    }
    sql += strings.Join(names, "=?,")
    if len(names) > 0 {
        sql += "=? "
    }
    return
}

//GetSelectsSQL 获取修改sql
func GetSelectsSQL(tableName string, data interface{}) (sql string) {
    val := reflect.ValueOf(data)
    if val.Kind() != reflect.Ptr && val.Kind() != reflect.Slice {
        return ""
    }
    sInd := reflect.Indirect(val)
    typ := sInd.Type().Elem()

    sql = "SELECT "
    var names []string
    for i := 0; i < typ.NumField(); i++ {
        name := GetFieldName(typ.Field(i), sqlTag)
        if name[0] == "-" {
            continue
        }
        names = append(names, name[0])
    }
    sql += strings.Join(names, ",")
    if len(names) > 0 {
        sql += " FROM " + tableName + " "
    }
    return
}


//GetSelectSQL 获取修改sql
func GetSelectSQL(tableName string, data interface{}) (sql string) {
    typ := reflect.TypeOf(data)
    if typ.Kind() != reflect.Ptr {
        return ""
    }
    sql = "SELECT "
    var names []string
    for i := 0; i < typ.Elem().NumField(); i++ {
        name := GetFieldName(typ.Elem().Field(i), sqlTag)
        if name[0] == "-" {
            continue
        }
        names = append(names, name[0])
    }
    sql += strings.Join(names, ",")
    if len(names) > 0 {
        sql += " FROM " + tableName + " "
    }
    return
}

//GetSelectsLIMITSQL 获取修改sql
func GetSelectsLIMITSQL(tableName string, data interface{}) (sql string) {
    val := reflect.ValueOf(data)
    if val.Kind() != reflect.Ptr && val.Kind() != reflect.Slice {
        return ""
    }
    sInd := reflect.Indirect(val)
    typ := sInd.Type().Elem()

    sql = ""
    var names []string
    for i := 0; i < typ.NumField(); i++ {
        name := GetFieldName(typ.Field(i), sqlTag)
        if name[0] == "-" {
            continue
        }
        names = append(names, name[0])
    }
    sql += strings.Join(names, ",")
    if len(names) > 0 {
        sql += " FROM " + tableName + " "
    }
    return
}

//GetFieldName GetFieldName
func GetFieldName(filed reflect.StructField, tagName string) []string {
    name := filed.Tag.Get(tagName)
    if name == "" {
        name = strings.ToLower(filed.Name)
        return []string{name}
    }
    return strings.Split(name, ",")

}

//// 判断值是否为空
//func isEmptyValue(v reflect.Value) bool {
//    if !v.IsValid() {
//        return true
//    }
//    switch v.Kind() {
//    case reflect.Array, reflect.Map, reflect.Slice, reflect.String:
//        return v.Len() == 0
//    case reflect.Bool:
//        return !v.Bool()
//    case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
//        return v.Int() == 0
//    case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64, reflect.Uintptr:
//        return v.Uint() == 0
//    case reflect.Float32, reflect.Float64:
//        return v.Float() == 0
//    case reflect.Interface, reflect.Ptr:
//        return v.IsNil()
//    }
//    return false
//}

